package ctn.dao.hibernate.impl;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Component;

import ctn.contant.FunContant;
import ctn.dao.inteface.CPAFunDao;
import ctn.hibernate.po.CpaFun;
import ctn.service.dto.FunDTO;
import ctn.util.jmesa.DTOFilter;
import ctn.util.jmesa.DTOSorter;
import ctn.util.jmesa.PaginationCriteria;

@Component
public class CPAFunDaoImpl extends AbstractCTNDAO implements CPAFunDao {

	@Override
	public List<Map<String, Object>> listFunInfoWithDoingStatus(final Long cpaId,
			final String status,final Date date,final Long userId) {
		return (List<Map<String, Object>>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session s)
					throws HibernateException, SQLException {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String dateStr = sdf.format(date);
				StringBuffer sb = new StringBuffer(" select f.plan_end_date as ped,f.fun_name as funname, ");
				sb.append(" datediff(date_format(f.plan_end_date,'%Y-%m-%d'),date_format(f.start_date,'%Y-%m-%d')) as p_s_diff, ");
				sb.append(" datediff(date_format(now(),'%Y-%m-%d'),date_format(f.plan_end_date,'%Y-%m-%d')) as n_p_diff, ");
				sb.append(" f.stage as stage, f.level as level, f.id as fid ");
				sb.append(" from cpa_fun f ");
				sb.append(" where date_format(f.plan_end_date,'%Y-%m-%d') < :date ");
				sb.append(" and f.status = :status ");
				sb.append(" and f.cpa_id = :cpa ");
				sb.append(" and f.impl_user_id = :user_id ");
				sb.append(" order by ped ");
				SQLQuery qry = s.createSQLQuery(sb.toString());
				qry.setParameter("status", status)
				   .setParameter("date", dateStr)
				   .setParameter("cpa", cpaId)
				   .setParameter("user_id", userId);
				qry.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				qry.addScalar(CPAFunDao.FUN_KEY_PED, Hibernate.DATE);
				qry.addScalar(CPAFunDao.FUN_KEY_FUN_NAME, Hibernate.STRING);
				qry.addScalar(CPAFunDao.FUN_KEY_LEVEL, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_STAGE, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_FID, Hibernate.LONG);
				qry.addScalar(CPAFunDao.FUN_KEY_P_S_DIFF, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_N_P_DIFF, Hibernate.INTEGER);
				List<Map<String, Object>> map = (List<Map<String, Object>>)qry.list();
				return map;
			}
		});
	}

	@Override
	public List<Map<String, Object>> listFunInfoWithCompleteStatus(final Long cpaId,
			final String status,final Long userId) {
		return (List<Map<String, Object>>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session s)
					throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer(" select f.impl_user_id as impluser,f.fun_name as funname, ");
				sb.append(" datediff(date_format(f.complete_date,'%Y-%m-%d'),date_format(f.plan_end_date,'%Y-%m-%d')) as c_p_diff, ");
				sb.append(" f.stage as stage, f.level as level, f.id as fid ");
				sb.append(" from cpa_fun f ");
				sb.append(" where f.status = :status ");
				sb.append(" and f.cpa_id = :cpa ");
				sb.append(" and f.impl_user_id = :user_id ");
				sb.append(" order by impluser,c_p_diff ");
				SQLQuery qry = s.createSQLQuery(sb.toString());
				qry.setParameter("status", status)
				   .setParameter("cpa", cpaId)
				   .setParameter("user_id", userId);
				qry.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				qry.addScalar(CPAFunDao.FUN_KEY_IMPLUSER, Hibernate.LONG);
				qry.addScalar(CPAFunDao.FUN_KEY_LEVEL, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_STAGE, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_FID, Hibernate.LONG);
				qry.addScalar(CPAFunDao.FUN_KEY_C_P_DIFF, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_FUN_NAME, Hibernate.STRING);
				List<Map<String, Object>> map = (List<Map<String, Object>>) qry.list();
				return map;
			}
		});
	}
	
	@Override
	public List<Map<String, Object>> listFunInfoWithMovedStatus(final Long cpaId,
			final String status,final Long userId) {
		return (List<Map<String, Object>>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session s)
					throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer(" select f.impl_user_id as impluser, ");
				sb.append(" f.stage as stage, f.level as level, f.id as fid ");
				sb.append(" from cpa_fun f ");
				sb.append(" where f.status = :status ");
				sb.append(" and f.cpa_id = :cpa ");
				sb.append(" and f.impl_user_id = :user_id ");
				sb.append(" order by impluser ");
				SQLQuery qry = s.createSQLQuery(sb.toString());
				qry.setParameter("status", status)
				   .setParameter("cpa", cpaId)
				   .setParameter("user_id", userId);
				qry.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				qry.addScalar(CPAFunDao.FUN_KEY_IMPLUSER, Hibernate.LONG);
				qry.addScalar(CPAFunDao.FUN_KEY_LEVEL, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_STAGE, Hibernate.INTEGER);
				qry.addScalar(CPAFunDao.FUN_KEY_FID, Hibernate.LONG);
				List<Map<String, Object>> map = (List<Map<String, Object>>)qry.list();
				return map;
			}
		});
	}
	
	public List<Map<String, Object>> getFunImplUserId(final Long cpaId,final String status,final Date date){
		
		return (List<Map<String, Object>>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session s)
					throws HibernateException, SQLException {
				String dateStr = "";
				StringBuffer sb = new StringBuffer(" select impl_user_id  ");
				sb.append(" from cpa_fun ");
				sb.append(" where status = :status ");
				if (date!=null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					dateStr = sdf.format(date);
					sb.append(" and date_format(plan_end_date,'%Y-%m-%d') < :date ");
				}
				sb.append(" and cpa_id = :cpa ");
				sb.append(" group by impl_user_id ");
				SQLQuery qry = s.createSQLQuery(sb.toString());
				qry.setParameter("status", status)
				   .setParameter("cpa", cpaId);
				if (date!=null) {
					qry.setParameter("date", dateStr);
				}
				qry.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				qry.addScalar(CPAFunDao.FUN_KEY_IMPL_USER_ID, Hibernate.LONG);
				List<Map<String, Object>> map = (List<Map<String, Object>>)qry.list();
				return map;
			}
		});
	}
	
	@Override
	public boolean recoverStatus(final Long cpa,final String status){
		
		return (Boolean)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Boolean doInHibernate(Session s)
					throws HibernateException, SQLException {
				SQLQuery qry = s.createSQLQuery("update cpa_fun f set f.status = :status where f.status=:statusReady and f.cpa_id=:cpa");
				qry.setParameter("status", status).setParameter("cpa", cpa).setParameter("statusReady", "R");
				int result = qry.executeUpdate();
				return result > 0;
			}
			
		});
	}
	
	@Override
	public boolean updateFunctionById(final String[] properties,final Object[] values,final long id){
		return (Boolean)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session s)
					throws HibernateException, SQLException {
				StringBuffer sql = new StringBuffer("update cpa_fun f set ");
				int len = properties.length;
				for (int i = 0;i < len;i++) {
					sql.append("f.").append(properties[i]).append("=:").append(properties[i]);
					if(i < len-1){
						sql.append(", ");
					}else{
						sql.append(" ");
					}
				}
				sql.append(" where f.id = ").append(":id");
				SQLQuery qry = s.createSQLQuery(sql.toString());
				len = values.length;
				for (int k = 0;k < len; k++) {
					qry.setParameter(properties[k], values[k]);
				}
				qry.setParameter("id", id);
				return qry.executeUpdate() > 0;
			}
			
		});
	}
	
	@Override
	public boolean updateFunctionByUserId(final String[] properties,final Object[] values,final long id,final long cpa,final String status){
		return (Boolean)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session s)
					throws HibernateException, SQLException {
				StringBuffer sql = new StringBuffer("update cpa_fun f set ");
				int len = properties.length;
				for (int i = 0;i < len;i++) {
					sql.append("f.").append(properties[i]).append("=:").append(properties[i]);
					if(i < len-1){
						sql.append(", ");
					}else{
						sql.append(" ");
					}
				}
				sql.append(" where f.impl_user_id = ").append(":user_id");
				sql.append(" and f.cpa_id = ").append(":cpa");
				if ("D".equals(status)) {
					sql.append(" and (f.status = ").append(":status1");
					sql.append(" or f.status = ").append(":status2)");
				}else if("C".equals(status)){
					sql.append(" and f.status = ").append(":status1");
				}
				SQLQuery qry = s.createSQLQuery(sql.toString());
				len = values.length;
				for (int k = 0;k < len; k++) {
					qry.setParameter(properties[k], values[k]);
				}
				qry.setParameter("user_id", id);
				qry.setParameter("cpa", cpa);
				qry.setParameter("status1", "D");
				if("D".equals(status))
					qry.setParameter("status2", "R");
				return qry.executeUpdate() > 0;
			}
			
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public Long getplugListCountByCriteria(final PaginationCriteria criteria) {
		return (Long) getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<DTOFilter> filters=criteria.getPageFilter().getFilters();
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT count(*) FROM CpaFun where 1=1 ");
				getFiler(filters, sb);
				
				Query query=session.createQuery(sb.toString());
				setParmeter(filters, query);
				return query.uniqueResult();
			}
			
		});
	}

	private void getFiler(List<DTOFilter> filters,StringBuffer sb){
		for(DTOFilter filter:filters){
			if("cpaid".equals(filter.getProperty())){
				sb.append(" AND cpa_id=:cpaid ");
			}
			if("verid".equals(filter.getProperty())){
				sb.append(" AND ver_id=:verid ");
			}
		}
	}
	private void setParmeter(List<DTOFilter> filters,Query query){
		for(DTOFilter filter:filters){
			if("cpaid".equals(filter.getProperty())){
				query.setParameter("cpaid", filter.getValue());
			}
			if("verid".equals(filter.getProperty())){
				query.setParameter("verid", filter.getValue());
			}
		}
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<FunDTO> getWfunListByCriteria(final PaginationCriteria criteria) {
		return (List<FunDTO>) getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<DTOFilter> filters=criteria.getPageFilter().getFilters();
				StringBuffer sb=new StringBuffer();
				sb.append("FROM CpaFun where 1=1 ");
				getFiler(filters, sb);
				getSorter(criteria.getPageSorter().getSorts(), sb);
				Query query=session.createQuery(sb.toString());
				setParmeter(filters, query);
				query.setFirstResult(criteria.getRowStart());
				query.setMaxResults(criteria.getRowEnd()
						- criteria.getRowStart());
				List<CpaFun> funs=query.list();
				List<FunDTO> fundtos= new ArrayList<FunDTO>();
				for (CpaFun fun : funs) {
					FunDTO dto=new FunDTO();
					FunContant.copyFunToDto(fun,dto);
					fundtos.add(dto);
				}
				
				return fundtos;
			}
		});
	}
	private void getSorter(List<DTOSorter> sorters,StringBuffer sb){
		if (sorters != null && sorters.size() > 0) {
			sb.append("order by ");
		} else {
			sb.append("ORDER BY ID ASC");
			return;
		}
		for (int i = 0; i < sorters.size() && sorters != null; i++) {
			DTOSorter sort = sorters.get(i);
			String sorterField = "";
			if ("ID".equals(sort.getProperty())) {
				sorterField = "ID";
			}
			if ("cpaName".equals(sort.getProperty())) {
				sorterField = "cpaName";
			}
			
			if ("createDate".equals(sort.getProperty())) {
				sorterField = "createDate";
			}
			if (i != sorters.size() - 1)
				sb.append(sorterField + " " + sort.getOrder() 
						+ ", ");
			else
				sb.append(sorterField + " " + sort.getOrder() );
		}
	}
	
	
	
	

	@Override
	public void saveFun(CpaFun fun) {
		
		getHibernateTemplate().save(fun);
	}
	
	@Override
	public void editFun(CpaFun fun) {
		getHibernateTemplate().update(fun);
		
	}

	@Override
	public CpaFun getCpaFunByID(Long id) {
		return getHibernateTemplate().load(CpaFun.class, id);
	}

	@Override
	public FunDTO getFunDTOByID(Long id) {
		CpaFun fun=getCpaFunByID(id);
		FunDTO dto=new FunDTO();
		FunContant.copyFunToDto(fun, dto);
		return dto;
	}
	
}

	
	

